package com.uxsino.commons.utils;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.uxsino.commons.model.JsonModel;

public class ExportUtil {

    private static Logger logger = LoggerFactory.getLogger(ExportUtil.class);

    public static JsonModel exportExcel(String sheetname, String title, List<JSONObject> tableHead, JSONArray rows,
        String fileName, HttpServletResponse response) {
        JsonModel jsonModel = new JsonModel(true, "操作成功");
        Date date = new Date();
        fileName = (fileName == null ? title + "_" + TimeUtils.format(date, "yyyyMMddHHmmss") : fileName) + ".xls";
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        ServletOutputStream ouputStream = null;
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = sheetname != null ? wb.createSheet(sheetname) : wb.createSheet();

            // 设置标题风格
            HSSFCellStyle titleStyle = createCellStyle(wb, (short) 18, true, HSSFCellStyle.ALIGN_CENTER,
                HSSFCellStyle.VERTICAL_BOTTOM, false);
            HSSFRow row = sheet.createRow(0);
            HSSFCell cells = row.createCell(tableHead.size());
            // 设置标题行高
            row.setHeight((short) 900);
            // 创建标题
            HSSFCell titleCell = row.createCell(0);
            titleCell.setCellValue(new HSSFRichTextString(title));
            titleCell.setCellStyle(titleStyle);
            cells.setCellType(HSSFCell.CELL_TYPE_BLANK);

            // 设置导出日期风格
            HSSFCellStyle dateStyle = createCellStyle(wb, null, true, HSSFCellStyle.ALIGN_RIGHT,
                HSSFCellStyle.VERTICAL_CENTER, false);
            row = sheet.createRow(1);
            // 创建导出日期
            HSSFCell dateCell = row.createCell(0);
            dateCell.setCellValue(new HSSFRichTextString("导出日期：" + TimeUtils.formatTime(date)));
            dateCell.setCellStyle(dateStyle);

            // 标题行合并
            CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, tableHead.size() - 1);
            sheet.addMergedRegion(titleRange);
            // 统计日期行合并
            CellRangeAddress dateRange = new CellRangeAddress(1, 1, 0, tableHead.size() - 1);
            sheet.addMergedRegion(dateRange);

            // 表头加粗
            HSSFCellStyle tableHeadStyle = createCellStyle(wb, null, true, HSSFCellStyle.ALIGN_LEFT,
                HSSFCellStyle.VERTICAL_CENTER, true);
            row = sheet.createRow(2);
            creatTableHead(row, tableHeadStyle, tableHead);

            HSSFCellStyle cellStyle = createCellStyle(wb, null, false, HSSFCellStyle.ALIGN_LEFT,
                HSSFCellStyle.VERTICAL_CENTER, true);
            for (int i = 0; i < rows.size(); i++) {
                int iHostRow = sheet.getLastRowNum();
                row = sheet.createRow(iHostRow + 1);
                createCells(row, cellStyle, rows.getJSONObject(i), tableHead);
            }

            wb.write(os);
            byte[] bytes = os.toByteArray();
            response.reset(); // 清空输出流
            response.setContentType("application/xls;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;fileName="
                    + new String(fileName.getBytes("UTF-8"), "ISO8859-1"));
            response.addHeader("Cache-Control", "no-cache");
            response.setContentLength(bytes.length);
            ouputStream = response.getOutputStream();
            ouputStream.write(bytes, 0, bytes.length);
            ouputStream.flush();
        } catch (Exception e) {
            logger.error("", e);
            jsonModel.setSuccess(false);
            jsonModel.setMsg("操作失败");
        } finally {
            try {
                if (ouputStream != null) {
                    ouputStream.close();
                }
                if (os != null) {
                    os.close();
                }
            } catch (IOException e) {
                logger.error("", e);
            }
        }
        return jsonModel;
    }

    public static void creatTableHead(HSSFRow row, HSSFCellStyle style, List<JSONObject> tableHead) {
        for (int i = 0; i < tableHead.size(); i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(tableHead.get(i).getString("tbText"));
            cell.setCellStyle(style);
        }
    }

    public static void createCells(HSSFRow row, HSSFCellStyle cellStyle, JSONObject obj, List<JSONObject> tableHead) {
        for (int i = 0; i < tableHead.size(); i++) {
            Object cellValue = obj.get(tableHead.get(i).getString("tbName"));
            HSSFCell cell = row.createCell(i);
            String valStr = null;
            if (cellValue == null) {
                valStr = "";
            } else if (cellValue instanceof Date) {
                valStr = TimeUtils.formatTime((Date) cellValue);
            } else if (cellValue instanceof Boolean) {
                valStr = (Boolean) cellValue ? "是" : "否";
            } else {
                valStr = String.valueOf(cellValue);
            }
            cell.setCellValue(valStr);
            cell.setCellStyle(cellStyle);
        }
    }

    public static HSSFCellStyle createCellStyle(HSSFWorkbook wb, Short fontHeight, boolean setBold, Short align,
        Short vertical, boolean setBorder) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        HSSFFont valueFont = wb.createFont();
        valueFont.setFontName("微软雅黑");
        if (null != fontHeight) {
            valueFont.setFontHeightInPoints(fontHeight);// 字体大小
        }
        if (setBold) {
            valueFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
        }
        cellStyle.setFont(valueFont);
        if (null != align) {
            cellStyle.setAlignment(align);
        }
        if (null != vertical) {
            cellStyle.setVerticalAlignment(vertical);
        }
        if (setBorder) {
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        }
        cellStyle.setWrapText(true);// 自动换行
        return cellStyle;
    }
}
